from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import os

db = SQLAlchemy()

class Employee(db.Model):
    __tablename__ = 'employee'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    gender = db.Column(db.String(10))
    birth_date = db.Column(db.String(20))
    ethnicity = db.Column(db.String(50))
    native_place = db.Column(db.String(100))
    birth_place = db.Column(db.String(100))
    political_status = db.Column(db.String(50))
    marital_status = db.Column(db.String(20))
    health_status = db.Column(db.String(100))
    phone = db.Column(db.String(20))
    email = db.Column(db.String(100), nullable=False)
    school = db.Column(db.String(100))
    major = db.Column(db.String(100))
    degree = db.Column(db.String(50))
    academic_degree = db.Column(db.String(50))  # 学位（学士、硕士、博士）
    graduation_date = db.Column(db.String(20))
    language_level = db.Column(db.String(100))
    title = db.Column(db.String(100))
    position_start_date = db.Column(db.String(20))
    current_company = db.Column(db.String(100))
    position = db.Column(db.String(100))
    contract_period_start = db.Column(db.String(20))
    contract_period_end = db.Column(db.String(20))
    current_address = db.Column(db.String(200))
    id_number = db.Column(db.String(18))
    household_address = db.Column(db.String(200))
    emergency_contact = db.Column(db.String(50))
    emergency_phone = db.Column(db.String(20))
    certificates = db.Column(db.Text)
    awards = db.Column(db.Text)
    skills = db.Column(db.Text)
    
    projects = db.relationship('Project', backref='employee', lazy=True, cascade='all, delete-orphan')
    work_experiences = db.relationship('WorkExperience', backref='employee', lazy=True, cascade='all, delete-orphan')

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'gender': self.gender,
            'birth_date': self.birth_date,
            'ethnicity': self.ethnicity,
            'native_place': self.native_place,
            'birth_place': self.birth_place,
            'political_status': self.political_status,
            'marital_status': self.marital_status,
            'health_status': self.health_status,
            'phone': self.phone,
            'email': self.email,
            'school': self.school,
            'major': self.major,
            'degree': self.degree,
            'academic_degree': self.academic_degree,
            'graduation_date': self.graduation_date,
            'language_level': self.language_level,
            'title': self.title,
            'position_start_date': self.position_start_date,
            'current_company': self.current_company,
            'position': self.position,
            'contract_period_start': self.contract_period_start,
            'contract_period_end': self.contract_period_end,
            'current_address': self.current_address,
            'id_number': self.id_number,
            'household_address': self.household_address,
            'emergency_contact': self.emergency_contact,
            'emergency_phone': self.emergency_phone,
            'certificates': self.certificates,
            'awards': self.awards,
            'skills': self.skills
        }

class Certificate(db.Model):
    __tablename__ = 'certificates'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    certificate_no = db.Column(db.String(50), nullable=False)
    level = db.Column(db.String(50))
    issuing_org = db.Column(db.String(100), nullable=False)
    issue_date = db.Column(db.Date, nullable=False)
    expiry_date = db.Column(db.Date)
    description = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 修改relationship定义，使用不同的backref名称
    employee = db.relationship('Employee', backref=db.backref('certificate_records', lazy=True))
    
    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'employee_name': self.employee.name if self.employee else None,
            'name': self.name,
            'certificate_no': self.certificate_no,
            'level': self.level,
            'issuing_org': self.issuing_org,
            'issue_date': self.issue_date.isoformat() if self.issue_date else None,
            'expiry_date': self.expiry_date.isoformat() if self.expiry_date else None,
            'description': self.description,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class CertificateAttachment(db.Model):
    __tablename__ = 'certificate_attachments'
    
    id = db.Column(db.Integer, primary_key=True)
    certificate_id = db.Column(db.Integer, nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filesize = db.Column(db.Integer, nullable=False)
    filetype = db.Column(db.String(100))
    description = db.Column(db.String(255))
    filepath = db.Column(db.String(500), nullable=False)
    upload_time = db.Column(db.DateTime, default=datetime.utcnow)
    uploader = db.Column(db.String(100), default='系统')
    
    def to_dict(self):
        return {
            'id': self.id,
            'certificate_id': self.certificate_id,
            'filename': self.filename,
            'filesize': self.filesize,
            'filetype': self.filetype,
            'description': self.description,
            'upload_time': self.upload_time.isoformat() if self.upload_time else None,
            'uploader': self.uploader
        }
    
    @classmethod
    def get_path(cls, instance=None, filename=None):
        """获取附件存储路径"""
        upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads', 'certificates')
        if not os.path.exists(upload_dir):
            os.makedirs(upload_dir)
        
        if instance and instance.id:
            return os.path.join(upload_dir, f"{instance.id}_{filename}")
        else:
            timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
            return os.path.join(upload_dir, f"{timestamp}_{filename}")

class Project(db.Model):
    __tablename__ = 'project'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    client_name = db.Column(db.String(100))
    role = db.Column(db.String(100))
    start_date = db.Column(db.String(20))
    end_date = db.Column(db.String(20))
    description = db.Column(db.Text)
    responsibilities = db.Column(db.Text)
    achievements = db.Column(db.Text)

    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'name': self.name,
            'client_name': self.client_name,
            'role': self.role,
            'start_date': self.start_date,
            'end_date': self.end_date,
            'description': self.description,
            'responsibilities': self.responsibilities,
            'achievements': self.achievements
        }

class WorkExperience(db.Model):
    __tablename__ = 'work_experience'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False)
    company = db.Column(db.String(100), nullable=False)
    department = db.Column(db.String(100))
    position = db.Column(db.String(100))
    start_date = db.Column(db.String(20))
    end_date = db.Column(db.String(20))
    description = db.Column(db.Text)
    reason_for_leaving = db.Column(db.Text)
    reference = db.Column(db.String(100))
    reference_phone = db.Column(db.String(20))

    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'company': self.company,
            'department': self.department,
            'position': self.position,
            'start_date': self.start_date,
            'end_date': self.end_date,
            'description': self.description,
            'reason_for_leaving': self.reason_for_leaving,
            'reference': self.reference,
            'reference_phone': self.reference_phone
        }

class ResumeTemplate(db.Model):
    __tablename__ = 'resume_template'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    content = db.Column(db.Text, nullable=False)
    template_type = db.Column(db.String(20))
    template_file = db.Column(db.String(200))
    preview_image = db.Column(db.String(200))
    is_default = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'content': self.content,
            'template_type': self.template_type,
            'template_file': self.template_file,
            'preview_image': self.preview_image,
            'is_default': self.is_default,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        } 

class Contract(db.Model):
    __tablename__ = 'contracts'

    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'))
    contract_number = db.Column(db.String(50), nullable=False)
    contract_type = db.Column(db.String(20), nullable=False)
    contract_party = db.Column(db.String(100), nullable=False)
    start_date = db.Column(db.DateTime, nullable=False)
    end_date = db.Column(db.DateTime, nullable=False)
    is_expired = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联关系
    employee = db.relationship('Employee', backref=db.backref('contracts', lazy=True))

    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'contract_number': self.contract_number,
            'contract_type': self.contract_type,
            'contract_party': self.contract_party,
            'start_date': self.start_date.isoformat() if self.start_date else None,
            'end_date': self.end_date.isoformat() if self.end_date else None,
            'is_expired': self.is_expired,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        } 

class ContractAttachment(db.Model):
    __tablename__ = 'contract_attachments'
    
    id = db.Column(db.Integer, primary_key=True)
    contract_id = db.Column(db.Integer, db.ForeignKey('contracts.id', ondelete='CASCADE'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filesize = db.Column(db.Integer, nullable=False)  # 文件大小，单位为字节
    filetype = db.Column(db.String(100))  # 文件MIME类型
    description = db.Column(db.String(255))
    filepath = db.Column(db.String(500), nullable=False)  # 文件在服务器上的存储路径
    upload_time = db.Column(db.DateTime, default=datetime.utcnow)
    uploader = db.Column(db.String(100), default='系统')
    
    # 关联关系
    contract = db.relationship('Contract', backref=db.backref('attachments', cascade='all, delete-orphan'))
    
    def to_dict(self):
        return {
            'id': self.id,
            'contract_id': self.contract_id,
            'filename': self.filename,
            'filesize': self.filesize,
            'filetype': self.filetype,
            'description': self.description,
            'upload_time': self.upload_time.isoformat() if self.upload_time else None,
            'uploader': self.uploader
        }
    
    @classmethod
    def get_path(cls, instance=None, filename=None):
        """获取附件存储路径"""
        upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads', 'contracts')
        if not os.path.exists(upload_dir):
            os.makedirs(upload_dir)
        
        if instance and instance.id:
            # 如果已有ID，则使用ID作为文件名前缀
            return os.path.join(upload_dir, f"{instance.id}_{filename}")
        else:
            # 否则使用时间戳
            timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
            return os.path.join(upload_dir, f"{timestamp}_{filename}")

class ResumeAttachment(db.Model):
    __tablename__ = 'resume_attachments'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id', ondelete='CASCADE'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filesize = db.Column(db.Integer, nullable=False)  # 文件大小，单位为字节
    filetype = db.Column(db.String(100))  # 文件MIME类型
    description = db.Column(db.String(255))
    filepath = db.Column(db.String(500), nullable=False)  # 文件在服务器上的存储路径
    upload_time = db.Column(db.DateTime, default=datetime.utcnow)
    uploader = db.Column(db.String(100), default='系统')
    
    # 关联关系
    employee = db.relationship('Employee', backref=db.backref('attachments', cascade='all, delete-orphan'))
    
    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'filename': self.filename,
            'filesize': self.filesize,
            'filetype': self.filetype,
            'description': self.description,
            'upload_time': self.upload_time.isoformat() if self.upload_time else None,
            'uploader': self.uploader
        }
    
    @classmethod
    def get_path(cls, instance=None, filename=None):
        """获取附件存储路径"""
        upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads', 'resumes')
        if not os.path.exists(upload_dir):
            os.makedirs(upload_dir)
        
        if instance and instance.id:
            # 如果已有ID，则使用ID作为文件名前缀
            return os.path.join(upload_dir, f"{instance.id}_{filename}")
        else:
            # 否则使用时间戳
            timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
            return os.path.join(upload_dir, f"{timestamp}_{filename}") 

class CertificateName(db.Model):
    __tablename__ = 'certificate_names'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    level = db.Column(db.String(20), nullable=False)  # 初级/中级/高级
    issuing_org = db.Column(db.String(100))  # 证书颁发机构
    is_deleted = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'level': self.level,
            'issuing_org': self.issuing_org,
            'is_deleted': self.is_deleted,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        } 

class TrainingExperience(db.Model):
    __tablename__ = 'training_experience'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False)
    course_name = db.Column(db.String(255), nullable=False)
    organization = db.Column(db.String(255), nullable=False)
    start_date = db.Column(db.String(32), nullable=False)
    end_date = db.Column(db.String(32), nullable=False)
    certificate = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'employee_id': self.employee_id,
            'course_name': self.course_name,
            'organization': self.organization,
            'start_date': self.start_date,
            'end_date': self.end_date,
            'certificate': self.certificate,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class TrainingAttachment(db.Model):
    __tablename__ = 'training_attachments'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    training_id = db.Column(db.Integer, db.ForeignKey('training_experience.id'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filesize = db.Column(db.Integer, nullable=False)
    filetype = db.Column(db.String(100))
    description = db.Column(db.String(255))
    filepath = db.Column(db.String(500), nullable=False)
    upload_time = db.Column(db.DateTime, default=datetime.utcnow)
    uploader = db.Column(db.String(100), default='系统')

    def to_dict(self):
        return {
            'id': self.id,
            'training_id': self.training_id,
            'filename': self.filename,
            'filesize': self.filesize,
            'filetype': self.filetype,
            'description': self.description,
            'filepath': self.filepath,
            'upload_time': self.upload_time.isoformat() if self.upload_time else None,
            'uploader': self.uploader
        }

    @classmethod
    def get_path(cls, instance=None, filename=None):
        """获取培训经历附件存储路径"""
        upload_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads', 'training_attachments')
        if not os.path.exists(upload_dir):
            os.makedirs(upload_dir)
        if instance and instance.id:
            return os.path.join(upload_dir, f"{instance.id}_{filename}")
        else:
            timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
            return os.path.join(upload_dir, f"{timestamp}_{filename}") 